package org.example.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.Data;
import org.apache.ibatis.annotations.Param;
import org.burningwave.core.assembler.StaticComponentContainer;
import org.example.listener.WebStudentListener;
import org.example.mapper.DAmountMapper;
import org.example.mapper.DApartmentMapper;
import org.example.mapper.DStaffMapper;
import org.example.pojo.DAmount;
import org.example.pojo.DApartment;
import org.example.pojo.DStaff;
import org.example.pojo.excel.AmountDownload;
import org.example.pojo.excel.DAmountExcel;
import org.example.pojo.excel.DStaffExcel;
import org.example.pojo.excel.UploadStatus;
import org.example.utils.Result;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;

import java.time.LocalDate;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.regex.Pattern;

@CrossOrigin(origins = "*")
@RestController
@RequestMapping("excel")
@Tag(name = "Excel文档交互API",description = "Upload Controller")
public class UploadController {

    @Autowired
    private DStaffMapper dStaffMapper;

    @Autowired
    private DAmountMapper dAmountMapper;

    @Autowired
    private DApartmentMapper dApartmentMapper;

    /**
     * 导入员工Excel数据表
     * @param multipartFile 上传的excel文件
     * @return  导入的excel数据
     */
    @Operation(summary = "导入员工Excel数据表")
    @PostMapping("/uploadStaff")
    @ResponseBody
    public Result<Map> UploadExcelStaff(HttpServletResponse response, HttpServletRequest request, MultipartFile multipartFile) throws IOException {
        StaticComponentContainer.Modules.exportAllToAll();
        InputStream inputStream = multipartFile.getInputStream();
        WebStudentListener<DStaffExcel> studentWebStudentListener = new WebStudentListener<>();
        EasyExcel.read(inputStream, DStaffExcel.class, studentWebStudentListener).sheet().doRead();
        List<DStaffExcel> excelData = studentWebStudentListener.getExcelData();
        System.out.println("excelData = " + excelData);
        for(DStaffExcel dStaffExcel : excelData){
            if(dStaffExcel.getStaffId()==null) return Result.warning(501,"请检查'员工号'标题是否错误！");
            if(dStaffExcel.getStaffName()==null) return Result.warning(501,"请检查'员工姓名'标题是否错误！");
            if(dStaffExcel.getApartmentId()==null) return Result.warning(501,"请检查'宿舍号'标题是否错误！");
            if(dStaffExcel.getStaffPhoneNumber()==null) return Result.warning(501,"请检查'标题联系电话'标题是否错误！");
            if(dStaffExcel.getStaffType()==null) return Result.warning(501,"请检查'员工职位'标题是否错误！");
            if(dStaffExcel.getStaffGroup()==null) return Result.warning(501,"请检查'员工部门'标题是否错误！");
        }

        for(DStaffExcel dStaffExcel : excelData){
            //检查员工号是否重复
            LambdaQueryWrapper<DStaff> dStaffLambdaQueryWrapper = new LambdaQueryWrapper<>();
            dStaffLambdaQueryWrapper.eq(DStaff::getStaffId,dStaffExcel.getStaffId());
            DStaff selectStaff = dStaffMapper.selectOne(dStaffLambdaQueryWrapper);
            if(selectStaff != null) return Result.warning(501,"员工号："+dStaffExcel.getStaffId()+",已存在相同员工号(导入停止)");

            LambdaQueryWrapper<DApartment> dApartmentLambdaQueryWrapper = new LambdaQueryWrapper<>();
            dApartmentLambdaQueryWrapper.eq(DApartment::getApartmentId,dStaffExcel.getApartmentId());
            DApartment selectApartment = dApartmentMapper.selectOne(dApartmentLambdaQueryWrapper);

            if(selectApartment == null ) {
                return Result.warning(501,"员工号"+dStaffExcel.getStaffId()+",选择的"+dStaffExcel.getApartmentId()+"宿舍不存在");
            }else{
                if(selectApartment.getVacancy() <= 0 ) return Result.warning(501,"员工号"+dStaffExcel.getStaffId()+",选择的"+dStaffExcel.getApartmentId()+"宿舍已满(导入停止)");

            }
            //插入数据
            DStaff dStaff = new DStaff();
            dStaff.setStaffId(dStaffExcel.getStaffId());
            dStaff.setStaffName(dStaffExcel.getStaffName());
            dStaff.setApartmentId(dStaffExcel.getApartmentId());
            dStaff.setStaffPhoneNumber(dStaffExcel.getStaffPhoneNumber());
            dStaff.setStaffType(dStaffExcel.getStaffType());
            dStaff.setStaffGroup(dStaffExcel.getStaffGroup());
            dStaff.setUpdateDate(new Date());
            dStaff.setCheckInDate(new Date());
            System.out.println("dStaff = " + dStaff);
            dStaffMapper.insert(dStaff);
        }

        Map excelDataMap = new HashMap();
        excelDataMap.put("data",excelData);
        return Result.ok(excelDataMap);
    }

    /**
     * 上传导入水电结算Excel表
     * @param multipartFile 上传的Excel文件
     */
    //保存excel备份路径
    @Value("${app.uploadExcelHydroElectric}")
    private String uploadExcelHydroElectric;
    //全局上传状态变量
    @Value("${app.uploadExcelStatus}")
    private Integer uploadExcelStatus;
    //全局上传结算日期
    @Value("${app.uploadDate}")
    private String uploadDate;

    //全局上传结算日期
    @Value("${app.timeLine}")
    private String timeLine;

    @Operation(summary = "上传导入水电结算Excel表")
    @PostMapping("/uploadHydroElectric")
    @ResponseBody
    public Result<Map> UploadHydroElectric(HttpServletResponse response,HttpServletRequest request,MultipartFile multipartFile) throws IOException {
        //使用easyExcel必要代码行，缺失会报错
        StaticComponentContainer.Modules.exportAllToAll();
        InputStream inputStream = multipartFile.getInputStream();

        WebStudentListener<DAmountExcel> studentWebStudentListener = new WebStudentListener<>();

        EasyExcel.read(inputStream, DAmountExcel.class, studentWebStudentListener).sheet().doRead();
        List<DAmountExcel> excelData = studentWebStudentListener.getExcelData();


        //检查获取是否为空，标题问题
        boolean ApartmentIsNull = true;
        boolean electricIsNull = true;
        boolean HydroIsNull = true;
        boolean HotWaterIsNull = true;
        for(DAmountExcel dAmountExcel : excelData){
            if(dAmountExcel.getApartmentId()!=null) ApartmentIsNull = false;
            if(dAmountExcel.getElectricAmount()!=null) electricIsNull = false;
            if(dAmountExcel.getHydroAmount()!=null) HydroIsNull=false;
            if(dAmountExcel.getHotWaterAmount()!=null) HotWaterIsNull=false;
        }
        if(ApartmentIsNull) {
            uploadExcelStatus = 0;
            return Result.warning(501,"请检查'房间号'标题是否错误！");
        }
        if(electricIsNull){
            uploadExcelStatus = 0;
            return Result.warning(501,"请检查'电费'标题是否错误！");
        }
        if(HydroIsNull){
            uploadExcelStatus = 0;
            return Result.warning(501,"请检查'水费'标题是否错误！");
        }
        if(HotWaterIsNull){
            uploadExcelStatus = 0;
            return Result.warning(501,"请检查'热水费'标题是否错误！");
        }


        //清除表所有数据
        dAmountMapper.deleteSettle();

        try {
            //格式化插入表数据的日期
            String dateStr = request.getParameter("uploadDate").split(Pattern.quote("(中国标准时间)"))[0].replace("GMT+0800", "GMT+08:00");
            SimpleDateFormat sdf = new SimpleDateFormat("E MMM dd yyyy HH:mm:ss z", Locale.US);
            Date date = sdf.parse(dateStr);
            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM");
            String formattedDate = sdf1.format(date);

            uploadDate = formattedDate;//记录结算时间

            //excel文件名的日期
            Date currentTime = new Date(System.currentTimeMillis());
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
            String formattedDateTime = formatter.format(currentTime);

            try {
                // 保存文件到服务器
                byte[] bytes = multipartFile.getBytes();
                Path path = Paths.get(uploadExcelHydroElectric + "水电(" + formattedDate + ")_" + formattedDateTime + ".xlsx");
                Files.write(path, bytes);

            } catch (IOException e) {
                e.printStackTrace();
            }

            //读出表内容存储新的excel
//            StaticComponentContainer.Modules.exportAllToAll();
//            String fileName = uploadExcelHydroElectric + "水电(" + formattedDate + ")_" + formattedDateTime + ".xlsx";
//            List<DAmountExcel> dStaffExcels = new ArrayList<>();

            //水电结算插入到表
            for(DAmountExcel dAmountExcel : excelData){
                dAmountExcel.setAmountDate(formattedDate);
                dAmountMapper.settle(dAmountExcel);
//                dStaffExcels.add(dAmountExcel);
            }

            try {
                dAmountMapper.calc(formattedDate);
            }catch (Exception e){
                System.out.println("存储过程调用错误");
            }


//            try {
//                Thread.sleep(8000);
//                System.out.println("等待了8秒钟后继续执行...");
//            } catch (InterruptedException e) {
//                System.out.println("等待过程中被中断：" + e.getMessage());
//            }

            //存储excel
//            EasyExcel.write(fileName, DAmountExcel.class).sheet("水电结算").doWrite(dStaffExcels);

        } catch (Exception e) {
            e.printStackTrace();
        }
        uploadExcelStatus = 4;
        return Result.ok(null);
    }

    //模拟调用存储过程导出
//    @Operation(summary = "模拟调用存储过程导出")
//    @PostMapping("/downloadHydroElectric")
//    @ResponseBody
//    public Result<Map> downloadHydroElectric(HttpServletResponse response,HttpServletRequest request,MultipartFile multipartFile) throws IOException {
//        return Result.ok(null);
//    }


    @Operation(summary = "获取当前水电计算状态")
    @PostMapping("/getUploadStatus")
    @ResponseBody
    public Result<Map> GetUploadStatus(@RequestBody UploadStatus uploadStatus){
        if(uploadStatus.getStatus() != null)
            uploadExcelStatus=uploadStatus.getStatus();
        Map resultMap = new HashMap();
        resultMap.put("uploadExcelStatus",uploadExcelStatus);
        resultMap.put("uploadDate",uploadDate);
//        resultMap.put("timeLine",uploadExcelStatus);
        return Result.ok(resultMap);
    }



    //导出水电表
    @Operation(summary = "导出excel水电表")
    @GetMapping("/downHydroElectricExcel")
    @ResponseBody
    public void downHydroElectricExcel(@RequestParam String month, HttpServletResponse response) throws IOException {

        //当前时间
        String excelDate = new DateTime().toString("yyyy-MM-dd__HH-mm-ss");

        //水电费数据
        List<AmountDownload> amountList = dAmountMapper.selectDownloadAmount(month);

        // 设置 Content-Type 为 Excel 的 MIME 类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 设置文件名（UTF-8 编码，避免中文乱码）
        String fileName = URLEncoder.encode("宿舍水电费("+excelDate+").xlsx", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName);

        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(response.getOutputStream(), AmountDownload.class).sheet("模板").doWrite(amountList);
    }


//    /**
//     * 上传导入水电结算Excel表
//     * @param multipartFile 上传的Excel文件
//     * @return  上传的Excel文件内数据
//     */
//    @PostMapping("/uploadHydroElectric")
//    @ResponseBody
//    public Result<Map> UploadHydroElectric(HttpServletResponse response, HttpServletRequest request, MultipartFile multipartFile) throws IOException {
//        //使用easyExcel必要代码行，缺失会报错
//        StaticComponentContainer.Modules.exportAllToAll();
//        InputStream inputStream = multipartFile.getInputStream();
//
//        WebStudentListener<DAmountExcel> studentWebStudentListener = new WebStudentListener<>();
//
//        EasyExcel.read(inputStream, DAmountExcel.class, studentWebStudentListener).sheet().doRead();
//        List<DAmountExcel> excelData = studentWebStudentListener.getExcelData();
//
//        //日期格式化
//        DecimalFormat df = new DecimalFormat("#.##");
//
//        //检查获取是否为空，标题问题
//        for(DAmountExcel dAmountExcel : excelData){
//            if(dAmountExcel.getAmountDate()==null) return Result.warning(501,"请检查'日期'标题是否错误！");
//            if(dAmountExcel.getApartmentId()==null) return Result.warning(501,"请检查'房间号'标题是否错误！");
//            if(dAmountExcel.getBuildingId()==null) return Result.warning(501,"请检查'楼号'标题是否错误！");
//            if(dAmountExcel.getElectric()==null) return Result.warning(501,"请检查'电'标题是否错误！");
//            if(dAmountExcel.getHydro()==null) return Result.warning(501,"请检查'电'标题是否错误！");
//            if(dAmountExcel.getElectricAmount()==null) return Result.warning(501,"请检查'电费'标题是否错误！");
//            if(dAmountExcel.getHydroAmount()==null) return Result.warning(501,"请检查'水费'标题是否错误！");
//
//            //格式化日期格式
//            DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//            DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern("yyyy-MM");
//            LocalDateTime dateTime = LocalDateTime.parse(dAmountExcel.getAmountDate(), inputFormatter);
//            String formattedDate = dateTime.toLocalDate().format(outputFormatter);
//            dAmountExcel.setAmountDate(formattedDate);
//
//            System.out.println("dAmountExcel = " + dAmountExcel);
//        }
//
//        for(DAmountExcel dAmountExcel : excelData){
//            LambdaQueryWrapper<DAmount> lambdaQueryWrapper = new LambdaQueryWrapper<>();
//            lambdaQueryWrapper.eq(DAmount::getApartmentId,dAmountExcel.getApartmentId())
//                    .eq(DAmount::getAmountDate,dAmountExcel.getAmountDate());
//            List<DAmount> dAmountList = dAmountMapper.selectList(lambdaQueryWrapper);
//
//            int staffNumber;
//            double avgElectricAmount, avgHydroAmount , avgHydro , avgElectric;
//
//            //存在记录,更改水电费记录
//            if(!dAmountList.isEmpty()){
//                staffNumber = dAmountList.size();
//                avgElectricAmount = (double) Math.round((dAmountExcel.getElectricAmount() / staffNumber) * 100) /100;
//                avgHydroAmount = (double) Math.round((dAmountExcel.getHydroAmount() / staffNumber) * 100) /100;
//                avgHydro = (double) Math.round((dAmountExcel.getHydro() / staffNumber) * 100) /100;
//                avgElectric = (double) Math.round((dAmountExcel.getElectric() / staffNumber) * 100) /100 ;
//
//                DAmount dAmount = new DAmount();
//                dAmount.setApartmentId(dAmountExcel.getApartmentId());
//                dAmount.setAmountElectric(avgElectricAmount);
//                dAmount.setAmountHydro(avgHydroAmount);
//                dAmount.setAmountDate(dAmountExcel.getAmountDate());
//                dAmount.setElectric(avgElectric);
//                dAmount.setHydro(avgHydro);
//                dAmountMapper.UpdateAmount(dAmount);
//            }
//
//            //不存在记录，新建记录
//            else{
//                DAmount dAmount = new DAmount();
//                dAmount.setAmountDate(dAmountExcel.getAmountDate());
//                dAmount.setApartmentId(dAmountExcel.getApartmentId());
//
//                List<String> staffIds = dStaffMapper.selectStaffByApartmentId_array(dAmountExcel.getApartmentId());
//                staffNumber = staffIds.size();
//
//                //导入的房间是否有入住人员
//                if(staffNumber != 0){
//                    for(String staffId : staffIds){
//                        avgElectricAmount = (double) Math.round((dAmountExcel.getElectricAmount() / staffNumber) * 100) /100;
//                        avgHydroAmount = (double) Math.round((dAmountExcel.getHydroAmount() / staffNumber) * 100) /100;
//                        avgHydro = (double) Math.round((dAmountExcel.getHydro() / staffNumber) * 100) /100;
//                        avgElectric = (double) Math.round((dAmountExcel.getElectric() / staffNumber) * 100) /100 ;
//
//                        dAmount.setAmountElectric(avgElectricAmount);
//                        dAmount.setAmountHydro(avgHydroAmount);
//                        dAmount.setElectric(avgHydro);
//                        dAmount.setHydro(avgElectric);
//                        dAmount.setStaffId(staffId);
//                        dAmount.setAmountStatus(1);
//                        dAmountMapper.insert(dAmount);
//                    }
//                }else{
//                    dAmount.setAmountElectric(dAmountExcel.getElectricAmount());
//                    dAmount.setAmountHydro(dAmountExcel.getHydroAmount());
//                    dAmount.setElectric(dAmountExcel.getHydro());
//                    dAmount.setHydro(dAmountExcel.getElectric());
//                    dAmount.setAmountStatus(1);
//                    dAmountMapper.insert(dAmount);
//                }
//
//            }
//
//        }
//
//        return Result.ok(null);
//    }
//
//    //导出路径（未启用）
//    String TestFileUtil = "C:/Users/chenhongkai/Desktop/测试excel导入系统/";
//    @PostMapping("/downloadStaff")
//    @ResponseBody
//    public Result<Map> DownloadStaff() {
//
//        StaticComponentContainer.Modules.exportAllToAll();
//
//        String fileName = TestFileUtil + "人员数据导出(" + System.currentTimeMillis() + ").xlsx";
//        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
//        // 如果这里想使用03 则 传入excelType参数即可
////		System.out.println("data() = " + data());
//        EasyExcel.write(fileName, DStaffExcel.class).sheet("宿舍人员数据").doWrite(dataStaff());
//
//        return Result.ok(null);
//    }
//    private List<DStaffExcel> dataStaff() {
//        List<DStaffExcel> list = new ArrayList<>();
//        List<DStaff> dStaffList = dStaffMapper.selectList(null);
//        for (DStaff dStaff : dStaffList) {
//            DStaffExcel dStaffExcel = new DStaffExcel();
//            dStaffExcel.setStaffId(dStaff.getStaffId());
//            dStaffExcel.setStaffName(dStaff.getStaffName());
//            dStaffExcel.setApartmentId(dStaff.getApartmentId());
//            dStaffExcel.setStaffPhoneNumber(dStaff.getStaffPhoneNumber());
//            dStaffExcel.setStaffType(dStaff.getStaffType());
//            dStaffExcel.setStaffGroup(dStaff.getStaffGroup());
//            list.add(dStaffExcel);
//        }
//        return list;
//    }


}
